#
# Author: Joao Tavanez n.3109    
# #Date: 21/04/2011
#
#

import sqlite3






# START OF STATISTICS
# total number of teachers in the higher education system per year;
def teachers_year():
    conn = sqlite3.connect('rebides.db')
    c = conn.cursor()
    
    cmd = "select id from fichas_docencia\
            WHERE ano = 0" 
           
    c.execute(cmd)

    nrTeachers0 = 0
    for row in c:
        nrTeachers0 = row[0]
        
        cmd = "select id from fichas_docencia\
            WHERE ano = 1" 
           
    c.execute(cmd)

    nrTeachers1 = 0
    for row in c:
        nrTeachers1 = row[0]
        
    cmd = "select id from fichas_docencia\
            WHERE ano = 2" 
           
    c.execute(cmd)

    nrTeachers2 = 0
    for row in c:
        nrTeachers2 = row[0]
        
    cmd = "select id from fichas_docencia\
            WHERE ano = 3" 
           
    c.execute(cmd)

    nrTeachers3 = 0
    for row in c:
        nrTeachers3 = row[0]
        
    cmd = "select id from fichas_docencia\
            WHERE ano = 4" 
           
    c.execute(cmd)

    nrTeachers4 = 0
    for row in c:
        nrTeachers4 = row[0]
        
    cmd = "select id from fichas_docencia\
            WHERE ano = 5" 
           
    c.execute(cmd)

    nrTeachers5 = 0
    for row in c:
        nrTeachers5 = row[0]
        
    cmd = "select id from fichas_docencia\
            WHERE ano = 6" 
           
    c.execute(cmd)

    nrTeachers6 = 0
    for row in c:
        nrTeachers6 = row[0]
        
    cmd = "select id from fichas_docencia\
            WHERE ano = 7" 
           
    c.execute(cmd)

    nrTeachers7 = 0
    for row in c:
        nrTeachers7 = row[0]
        
        cmd = "select id from fichas_docencia\
            WHERE ano = 8" 
           
    c.execute(cmd)

    nrTeachers8 = 0
    for row in c:
        nrTeachers8 = row[0]
        
    cmd = "select id from fichas_docencia\
            WHERE ano = 9" 
           
    c.execute(cmd)

    nrTeachers9 = 0
    for row in c:
        nrTeachers9 = row[0]
        
    return nrTeachers0, nrTeachers1, nrTeachers2, nrTeachers3, nrTeachers4, nrTeachers5, nrTeachers6, nrTeachers7, nrTeachers8, nrTeachers9
    pass

# total number of teachers per institution and per year(onlu year 2000);
def teachers_institution():
    conn = sqlite3.connect('rebides.db')
    a = conn.cursor()
   
    cmd = "select id, ano, tipo_estabelecimento from fichas_docencia\
            WHERE tipo_estabelecimento LIKE '%policial%' AND ano =0" 
           
    a.execute(cmd)

    teacherCounter = 0
    for row in a:
        teacherCounter = teacherCounter +1
        
    cmd = "select id, ano, tipo_estabelecimento from fichas_docencia\
            WHERE tipo_estabelecimento LIKE '%cooperativo poli%' AND ano =0 " 
           
    a.execute(cmd)

    teacherCounter1 = 0
    for row in a:
        teacherCounter1 = teacherCounter1 +1
        
    cmd = "select id, ano, tipo_estabelecimento from fichas_docencia\
            WHERE tipo_estabelecimento LIKE '%blico polit%' AND ano =0 " 
           
    a.execute(cmd)
    
    teacherCounter2 = 0
    for row in a:
        teacherCounter2 = teacherCounter2 +1
    
    
    cmd = "select id, ano, tipo_estabelecimento from fichas_docencia\
            WHERE tipo_estabelecimento LIKE '%%blico univ%' AND ano =0" 
           
    a.execute(cmd)

    teacherCounter3 = 0
    for row in a:
        teacherCounter3 = teacherCounter3 +1
        
    cmd = "select id, ano, tipo_estabelecimento from fichas_docencia\
            WHERE tipo_estabelecimento LIKE '%cooperativo poli%' AND ano =0 " 
           
    a.execute(cmd)

    teacherCounter4 = 0
    for row in a:
        teacherCounter4 = teacherCounter4 +1
        
    cmd = "select id, ano, tipo_estabelecimento from fichas_docencia\
            WHERE tipo_estabelecimento LIKE '%privado univ%' AND ano =0 " 
           
    a.execute(cmd)

    teacherCounter5 = 0
    for row in a:
        teacherCounter5 = teacherCounter5 +1
        
    cmd = "select id, ano, tipo_estabelecimento from fichas_docencia\
            WHERE tipo_estabelecimento LIKE '%privado poli%' AND ano =0 " 
           
    a.execute(cmd)

    teacherCounter6 = 0
    for row in a:
        teacherCounter6 = teacherCounter6 +1
            
    return teacherCounter, teacherCounter1, teacherCounter2, teacherCounter3, teacherCounter4,  teacherCounter5,  teacherCounter6
    pass

# Total of Teachers degree's in 2009
def teachers_degree():
    conn = sqlite3.connect('rebides.db')
    b = conn.cursor()
    
    cmd = "select * from fichas_docencia\
             WHERE grau LIKE '%licenciado%' and ano = 9"
    b.execute(cmd)
    teacherLic = 0
    for row in b:
        teacherLic = teacherLic +1
        
    cmd = "select * from fichas_docencia\
             WHERE grau LIKE '%mestre%' and ano = 9"
    b.execute(cmd)
    teacherMest = 0
    for row in b:
        teacherMest = teacherMest +1
        
    cmd = "select * from fichas_docencia\
             WHERE grau LIKE '%dout%' and ano = 9"
    b.execute(cmd)
    TeacherDout = 0
    for row in b:
        TeacherDout = TeacherDout +1
        
    return teacherLic, teacherMest, TeacherDout

#list of institution by year
def instit_year():
    conn = sqlite3.connect('rebides.db')
    d = conn.cursor()
    
    cmd = "select estabelecimento from fichas_docencia \
            where ano = 0 group by estabelecimento"
    d.execute(cmd)
    count = 0
    for row in d:
        count = count + 1
    
    cmd = "select estabelecimento from fichas_docencia\
            where ano = 1 group by estabelecimento"
    d.execute(cmd)
    count1 = 0
    for row in d:
        count1 = count1 + 1
        
    cmd = "select estabelecimento from fichas_docencia\
            where ano = 2 group by estabelecimento"
    d.execute(cmd)
    count2 = 0
    for row in d:
        count2 = count2 + 1
        
    cmd = "select estabelecimento from fichas_docencia\
            where ano = 3 group by estabelecimento"
    d.execute(cmd)
    count3 = 0
    for row in d:
        count3 = count3 + 1
        
    cmd = "select estabelecimento from fichas_docencia\
            where ano = 4 group by estabelecimento"
    d.execute(cmd)
    count4 = 0
    for row in d:
        count4 = count4 + 1
        
    cmd = "select estabelecimento from fichas_docencia\
            where ano = 5 group by estabelecimento"
    d.execute(cmd)
    count5 = 0
    for row in d:
        count5 = count5 + 1
        
    cmd = "select estabelecimento from fichas_docencia\
            where ano = 6 group by estabelecimento"
    d.execute(cmd)
    count6 = 0
    for row in d:
        count6 = count6 + 1
        
    cmd = "select estabelecimento from fichas_docencia\
            where ano = 7 group by estabelecimento"
    d.execute(cmd)
    count7 = 0
    for row in d:
        count7 = count7 + 1
        
    cmd = "select estabelecimento from fichas_docencia\
            where ano = 8 group by estabelecimento"
    d.execute(cmd)
    count8 = 0
    for row in d:
        count8 = count8 + 1
        
    cmd = "select estabelecimento from fichas_docencia\
            where ano = 9 group by estabelecimento"
    d.execute(cmd)
    count9 = 0
    for row in d:
        count9 = count9 + 1
        
    return count, count1, count2, count3, count4, count5, count6, count7, count8, count9

